﻿using log4net;
using System;
using System.Data.SQLite;
using System.Reflection;
using System.Text.RegularExpressions;
using WpfApp1.Acme.ApplicationAttribute;
using WpfApp1.Acme.ApplicationClass;
using WpfApp1.Acme.Template;

namespace WpfApp1.Acme.DataSourcePackage {
    /// <summary>
    /// SQLite数据库的操作类
    /// </summary>
    [Component]
    internal class SQLiteDataSourceOperation : DataSourceOperation {
        /** 日志对象 */
        private static readonly ILog logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        private SQLiteConnection sqlConnection;
        /// <summary>
        /// 注册数据源策略池
        /// </summary>
        public void beanInitializingBefore() {
            DataSourceContext.registStraytegy(typeof(SQLiteConnection).Name, this);
        }

        /// <summary>
        /// 数据库连接方法
        /// </summary>
        /// <param name="dataSourceClass">数据库连接类</param>
        /// <returns></returns>
        public bool connection(object dataSourceClass) {
            bool isConnecton = false;
            try {
                sqlConnection = (SQLiteConnection)dataSourceClass;
                ((SQLiteConnection)dataSourceClass).Open();
                isConnecton = true;
            } catch (Exception e) {
                logger.Error(e);
            }
            return isConnecton;
        }

        /// <summary>
        /// ExecuteNonQuery主要是对数据库的修改、插入、删除来使用，当使用普通的SQL语句
        /// 拼接来执行SQL语句时，应使用$来对SQL字符串进行拼接，如下：
        /// $"insert into testTable(id, name, password) values({2}, {"'tang123'"}, {"'1312312'"})"
        /// 值的注意的是在传递字符串类型的参数时需要使用单引号在内部将字符串内容括起来,如下：
        /// string name = "tang123";
        /// string sql = $"insert into testTable(id, name, password) values({2}, {"'" + name + "'"}
        /// , {"'1312312'"})";
        /// 如果没有使用单引号那么在执行SQL语句的时候就会将该字符串类型参数解析成数据库表中的一列
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="sqlParameters">SQL语句参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(in string sqlString, params object[] sqlParameters) {
            int result = 0;
            try {
                int index = 0;
                SQLiteCommand sqliteCommand = sqlConnection.CreateCommand();
                sqliteCommand.CommandText = sqlString;

                // 防止SQL注入
                if (sqlString.Contains("@") && sqlParameters.Length > 0) {
                    foreach (Match match in Regex.Matches(sqlString, "@.*?(?=,|\\)|\\s)|@.*?[a-z]$|[A-Z]$")) { 
                       sqliteCommand.Parameters.Add(new SQLiteParameter(match.Value, sqlParameters[index++]));
                    }
                }
                result = sqliteCommand.ExecuteNonQuery();

            } catch (Exception e) {
                logger.Error(e);
            }
            return result;
        }

        /// <summary>
        /// ExecuteReader方法是用于SQL语句的查询语句来使用
        /// 和RowMapper<T>接口配合使用可以更好的简化数据库
        /// 与实体类的映射
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="objectMapper">RowMapper<T>接口的实现类</param>
        /// <param name="sqlParameters">SQL语句参数</param>
        /// <returns></returns>
        public T ExecuteReader<T>(in string sqlString, object objectMapper, params object[] sqlParameters) {
            
            SQLiteDataReader reader = null;
            T template = default(T);
            try {
                int index = 0;
                SQLiteCommand sqliteCommand = sqlConnection.CreateCommand();
                sqliteCommand.CommandText = sqlString;

                if (sqlString.Contains("@") && sqlParameters.Length > 0) {
                    foreach (Match match in Regex.Matches(sqlString, "@.*?(?=,|\\)|\\s)|@.*?[a-z]$|[A-Z]$")) {
                        sqliteCommand.Parameters.Add(new SQLiteParameter(match.Value, sqlParameters[index++]));
                    }
                }

                reader = sqliteCommand.ExecuteReader();
                if (typeof(RowMapper<T>).IsAssignableFrom(objectMapper.GetType())) {
                    while (reader.Read()) {
                        template = ((RowMapper<T>)objectMapper).mapRow(reader);
                    }
                } else {
                    logger.Error(objectMapper.GetType().FullName + " 未实现RowMapper<>接口");
                }

            } catch (Exception e) {
                logger.Error(e);
            }
            return template;
        }

        /// <summary>
        /// 返回SQL语句的第一行第一列
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns></returns>
        public object Executescalar(in string sqlString, params object[] sqlParameters) {
            object obj = null;
            try {
                int index = 0;
                SQLiteCommand sqliteCommand = sqlConnection.CreateCommand();
                sqliteCommand.CommandText = sqlString;

                if (sqlString.Contains("@") && sqlParameters.Length > 0) {
                    foreach (Match match in Regex.Matches(sqlString, "@.*?(?=,|\\)|\\s)|@.*?[a-z]$|[A-Z]$")) {
                        sqliteCommand.Parameters.Add(new SQLiteParameter(match.Value, sqlParameters[index++]));
                    }
                }

                obj = sqliteCommand.ExecuteScalar();
            } catch (Exception e) {
                logger.Error(e);
            }
            return obj;
        }

        /// <summary>
        /// 数据库连接资源释放方法
        /// </summary>
        public void close() {
            sqlConnection.Close();
            logger.Info("已关闭数据库服务器: " + sqlConnection.DataSource);
        }
    }
}
